package mobi.weq.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import mobi.weq.db.Database;
import mobi.weq.model.Observation;
import mobi.weq.model.SensorObservation;
import mobi.weq.util.Util;
import mobi.weq.util.edinburgh.EdinburghMap;

public class SensorObservationDao {
	private String tableName = "so";

	public String getNDaysBackTable(int nDays) {
		Date d = new Date();
		d.setDate(22);
		d.setMonth(6);

		Util.decreaseDateByDay(d, nDays);

		return "so_" + d.getDate() + "_" + (d.getMonth() + 1) + "_" + (d.getYear() + 1900);
	}

	public synchronized int addObservation(SensorObservation ob) throws SQLException {
		Connection c = Database.getConnection();
		String query = "insert into " + tableName + "(id,city,no2,so2,o3,pm10,pm2p5,latitude,longitude,timestamp) values(?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = c.prepareStatement(query);
		ps.setString(1, ob.getId());
		ps.setString(2, ob.getCity());
		ps.setDouble(3, ob.getNo2());
		ps.setDouble(4, ob.getSo2());
		ps.setDouble(5, ob.getO3());
		ps.setDouble(6, ob.getPm10());
		ps.setDouble(7, ob.getPm2p5());
		ps.setDouble(8, ob.getLatitude());
		ps.setDouble(9, ob.getLongitude());
		Date d = ob.getTimestamp();
		if (d != null)
			ps.setTimestamp(10, new java.sql.Timestamp(d.getTime()));
		else
			ps.setDate(10, null);

		return ps.executeUpdate();

	}

	public synchronized int[] addObservations(ArrayList<SensorObservation> records) throws SQLException {
		Connection c = Database.getConnection();
		String query = "insert into " + tableName + "(id,city,no2,so2,o3,pm10,pm2p5,latitude,longitude,timestamp) values(?,?,?,?,?,?,?,?,?,?)";

		PreparedStatement ps = c.prepareStatement(query);
		c.setAutoCommit(false);

		for (SensorObservation ob : records) {
			ps.setString(1, ob.getId());
			ps.setString(2, ob.getCity());
			ps.setDouble(3, ob.getNo2());
			ps.setDouble(4, ob.getSo2());
			ps.setDouble(5, ob.getO3());
			ps.setDouble(6, ob.getPm10());
			ps.setDouble(7, ob.getPm2p5());
			ps.setDouble(8, ob.getLatitude());
			ps.setDouble(9, ob.getLongitude());
			Date d = ob.getTimestamp();
			if (d != null)
				ps.setTimestamp(10, new java.sql.Timestamp(d.getTime()));
			else
				ps.setDate(10, null);
			ps.addBatch();
		}
		int[] counts = ps.executeBatch();
		c.commit();

		System.out.println(counts.length + " :Rows Added");
		return counts;

	}

	public synchronized int addObservationInTable(SensorObservation ob, String table) throws SQLException {
		Connection c = Database.getConnection();
		String query = "insert into " + table + "(id,city,no2,so2,o3,pm10,pm2p5,latitude,longitude,timestamp) values(?,?,?,?,?,?,?,?,?,?)";
		PreparedStatement ps = c.prepareStatement(query);
		ps.setString(1, ob.getId());
		ps.setString(2, ob.getCity());
		ps.setDouble(3, ob.getNo2());
		ps.setDouble(4, ob.getSo2());
		ps.setDouble(5, ob.getO3());
		ps.setDouble(6, ob.getPm10());
		ps.setDouble(7, ob.getPm2p5());
		ps.setDouble(8, ob.getLatitude());
		ps.setDouble(9, ob.getLongitude());
		Date d = ob.getTimestamp();
		if (d != null)
			ps.setTimestamp(10, new java.sql.Timestamp(d.getTime()));
		else
			ps.setDate(10, null);

		return ps.executeUpdate();

	}

	public synchronized int[] addObservationsInTable(ArrayList<SensorObservation> records, String table) throws SQLException {
		Connection c = Database.getConnection();
		String query = "insert into " + table + "(id,city,no2,so2,o3,pm10,pm2p5,latitude,longitude,timestamp) values(?,?,?,?,?,?,?,?,?,?)";

		PreparedStatement ps = c.prepareStatement(query);
		c.setAutoCommit(false);

		for (SensorObservation ob : records) {
			ps.setString(1, ob.getId());
			ps.setString(2, ob.getCity());
			ps.setDouble(3, ob.getNo2());
			ps.setDouble(4, ob.getSo2());
			ps.setDouble(5, ob.getO3());
			ps.setDouble(6, ob.getPm10());
			ps.setDouble(7, ob.getPm2p5());
			ps.setDouble(8, ob.getLatitude());
			ps.setDouble(9, ob.getLongitude());
			Date d = ob.getTimestamp();
			if (d != null)
				ps.setTimestamp(10, new java.sql.Timestamp(d.getTime()));
			else
				ps.setDate(10, null);
			ps.addBatch();
		}
		int[] counts = ps.executeBatch();
		c.commit();

		System.out.println(counts.length + " :Rows Added");
		return counts;

	}

	public List<SensorObservation> getAllObservations() throws SQLException {

		Connection c = Database.getConnection();
		String query = "select * from " + tableName;
		PreparedStatement ps = c.prepareStatement(query);

		ResultSet rs = ps.executeQuery();
		List<SensorObservation> observations = getObservationsFromResultSet(rs);
		return observations;
	}

	public List<SensorObservation> getLatestObservationForThePosition(double lat, double lng) throws SQLException {
		Connection c = Database.getConnection();
		String query = "select * from " + tableName
				+ " where ? >= latitude AND ? <=latitude AND  ? >= longitude AND ? <=longitude order by timestamp desc limit 1";
		PreparedStatement ps = c.prepareStatement(query);
		ps.setDouble(1, lat);
		ps.setDouble(2, lat - EdinburghMap.GRID_SIZE);
		ps.setDouble(3, lng);
		ps.setDouble(4, lng - EdinburghMap.GRID_SIZE);
		ResultSet rs = ps.executeQuery();

		List<SensorObservation> observations = getObservationsFromResultSet(rs);
		return observations;
	}

	private List<SensorObservation> getObservationsFromResultSet(ResultSet rs) throws SQLException {
		List<SensorObservation> observations = new ArrayList<SensorObservation>();

		while (rs.next()) {
			SensorObservation ob = new SensorObservation();
			ob.setId(rs.getString(1));
			ob.setCity(rs.getString(2));
			ob.setNo2(rs.getDouble(3));
			ob.setSo2(rs.getDouble(4));
			ob.setO3(rs.getDouble(5));
			ob.setPm10(rs.getDouble(6));
			ob.setPm2p5(rs.getDouble(7));
			ob.setLatitude(rs.getDouble(8));
			ob.setLongitude(rs.getDouble(9));
			ob.setTimestamp(rs.getTimestamp(10));
			observations.add(ob);
		}
		return observations;
	}

	public synchronized int deleteObservation(Observation ob) throws SQLException {

		Connection cn = Database.getConnection();
		String sql = "delete from " + tableName + " where ?=?";
		PreparedStatement ps = cn.prepareStatement(sql);
		return ps.executeUpdate();
	}

	public List<SensorObservation> getGridsWithLatestObservationInTheBounds(double latitude1, double longitude1, double latitude2, double longitude2, int hour)
			throws SQLException {
		Connection c = Database.getConnection();
		String table = getNDaysBackTable(0);
		System.out.println(table);
		String query = "select * from " + table + " where latitude >= ? and latitude <= ? and longitude >= ? and longitude <= ? and HOUR(timestamp)=?";

		System.out.println(query);
		PreparedStatement ps = c.prepareStatement(query);
		ps.setDouble(1, latitude1 - EdinburghMap.GRID_SIZE);
		ps.setDouble(2, latitude2 + EdinburghMap.GRID_SIZE);
		ps.setDouble(3, longitude1 - EdinburghMap.GRID_SIZE);
		ps.setDouble(4, longitude2 + EdinburghMap.GRID_SIZE);
		ps.setInt(5, hour);

		System.out.println(ps.toString());
		ResultSet rs = ps.executeQuery();

		return getObservationsFromResultSet(rs);
	}
}
